﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Controls.Primitives;

namespace K36_104_047_NguyenVanCaoNguyen
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private SqlConnection c = new SqlConnection(Properties.Settings.Default.ConnStr);
        SqlDataAdapter a;
        private DataSet d;
        public MainWindow()
        {
            InitializeComponent();
            caQue.SelectedDate = DateTime.Today;
        }

        void FillData(string sqlcmd, string path, DataGrid grid)
        {
            a = new SqlDataAdapter(sqlcmd, c);
            d = new DataSet();
            a.Fill(d, path);

            grid.DataContext = d;
        }

        private void buttonSave_Click(object sender, RoutedEventArgs e)
        {
            SqlCommandBuilder builder = new SqlCommandBuilder(a);
            try
            {
                a.UpdateCommand = builder.GetUpdateCommand();
                a.Update(d, ((Button)sender).Name);
            }
            catch
            {
                MessageBox.Show("Có lỗi xảy ra trong quá trình cập nhật dữ liệu");
                return;
            }
            MessageBox.Show("Cập nhật thành công");
        }

        private void medSelected(object sender, SelectionChangedEventArgs e)
        {
            //DataGrid dg = (DataGrid)sender;
            //DataRowView row = (DataRowView)dg.SelectedItems[0];
        }

        private void tabMed_Click(object sender, RoutedEventArgs e)
        {
            FillData("SELECT ROW_NUMBER() OVER(ORDER BY mid DESC) AS row, mname, mprodate, mexpdate, mprice, mid, uid, tid FROM Medicines", "pathMed", medGrid);
            tabMed.IsSelected = true;
        }

        private void tabQue_Click(object sender, RoutedEventArgs e)
        {
            caQue.SelectedDate = DateTime.Today;
            //FillData("SELECT ROW_NUMBER() OVER(ORDER BY lday DESC) AS row, l.pid, pname, pbirthday, pgender, paddress FROM Lists AS l, Patients AS p WHERE l.pid = p.pid AND lday = GETDATE()", "pathQue", queGrid);
            tabQue.IsSelected = true;
        }

        private void caQue_SelectedDatesChanged(object sender, SelectionChangedEventArgs e)
        {
            string cmd = "SELECT ROW_NUMBER() OVER(ORDER BY lday DESC) AS row, l.lday, l.pid, pname, pbirthday, pgender, paddress FROM Lists AS l, Patients AS p WHERE l.pid = p.pid AND l.lday = convert(datetime, '" + caQue.SelectedDate.Value.ToString("M/dd/yyyy") + "', 101)"; 
            FillData(cmd, "pathQue", queGrid);
        }

        private void tabPat_Click(object sender, RoutedEventArgs e)
        {
            string cmd = "SELECT ROW_NUMBER() OVER(ORDER BY pid DESC) AS row, pid, pname, paddress, pbirthday, pgender, pemail FROM Patients";
            FillData(cmd, "pathPat", patGrid);
            tabPat.IsSelected = true;
        }

        private void filterPat_Click(object sender, RoutedEventArgs e)
        {
            bool filterSet = false;
            StringBuilder sb = new StringBuilder("WHERE 1=1 ");
            if (nameFilterCheck.IsChecked.HasValue && nameFilterCheck.IsChecked.Value && nameFilter.Text.Length > 0)
            {
                sb.AppendFormat("AND p.pname LIKE '%{0}%' ", nameFilter.Text);
                filterSet = true;
            }

            if (emailFilterCheck.IsChecked.HasValue && emailFilterCheck.IsChecked.Value && emailFilter.Text.Length > 0)
            {
                var regEMail = new System.Text.RegularExpressions.Regex(@"^[a-zA-Z][\w\.-]{2,28}[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$");
                if (!regEMail.IsMatch(emailFilter.Text))
                {
                    MessageBox.Show("email không hợp lệ");
                    emailFilter.Focus();
                    return;
                }
                else
                {
                    sb.AppendFormat("AND p.pemail = N'{0}' ", emailFilter.Text);
                    filterSet = true;
                }
            }

            if (genderFilterCheck.IsChecked.HasValue && genderFilterCheck.IsChecked.Value)
            {
                if (femaleFilter.IsChecked.HasValue && femaleFilter.IsChecked.Value)
                {
                    sb.Append("AND p.pgender = 1 ");
                }
                else
                {
                    sb.Append("AND p.pgender = 0 ");
                }
                filterSet = true;
            }

            if (birthFilterCheck.IsChecked.HasValue && birthFilterCheck.IsChecked.Value)
            {
                if (birthFromFilter.SelectedDate.HasValue && birthToFilter.SelectedDate.HasValue)
                {
                    if (birthFromFilter.SelectedDate.Value > birthToFilter.SelectedDate.Value)
                    {
                        MessageBox.Show("Giới hạn thời gian không phù hợp");
                        return;
                    }
                    else
                    {
                        sb.AppendFormat("AND p.pbirthday BETWEEN convert(datetime, '{0}', 101) AND convert(datetime, '{1}', 101) ", birthFromFilter.SelectedDate.Value.ToString("M/dd/yyyy"), birthToFilter.SelectedDate.Value.ToString("M/dd/yyyy"));
                        filterSet = true;
                    }
                }
                else if (birthFromFilter.SelectedDate.HasValue)
                {
                    sb.AppendFormat("AND p.pbirthday >= '{0}' ", birthFromFilter.SelectedDate.Value.ToString("M/dd/yyyy"));
                    filterSet = true;
                }
                else if(birthToFilter.SelectedDate.HasValue)
                {
                    sb.AppendFormat("AND p.pbirthday <= '{0}' ", birthToFilter.SelectedDate.Value.ToString("M/dd/yyyy"));
                    filterSet = true;
                }
            }
            var cmd = "SELECT ROW_NUMBER() OVER(ORDER BY p.pid, e.edate DESC) as row, e.eid, p.pname, e.edate, e.esymtom, d.dname FROM Patients AS p LEFT OUTER JOIN ExamDetails AS e INNER JOIN Diseases AS d ON e.did = d.did ON p.pid = e.pid "
                + (filterSet ? sb.ToString() : "") + " ORDER BY p.pid, e.edate";
            a = new SqlDataAdapter(cmd, c);
            d = new DataSet();
            a.Fill(d, "pathPatFilter");
            patFilterGrid.DataContext = d;
        }

        private void HyperlinkDel_Click(object sender, RoutedEventArgs e)
        {
            DataRowView row = (DataRowView)queGrid.SelectedItem;
            DateTime dt = DateTime.Parse(row["lday"].ToString());
            string cmdStr = "DELETE FROM Lists WHERE lday = convert(datetime, '" + dt.ToString("MM-dd-yyyy") + "', 101) AND pid =" + row["pid"].ToString();
            SqlCommand cmd = new SqlCommand(cmdStr, c);
            int n = 0;
            try
            {
                c.Open();
                n = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Có lỗi xảy ra khi xóa lịch hẹn:" + ex.Message);
                return;
            }
            finally
            {
                c.Close();
            }
            MessageBox.Show(String.Format("Xóa hoàn tất {0} lịch hẹn trong ngày {1}", n, row[0].ToString()));
            row.Delete();
        }
        private void HyperlinkEdit_Click(object sender, RoutedEventArgs e)
        {

        }
    }
}
